package ac.zlf.springmybatis.core.mybatis.util;

import ac.zlf.springmybatis.core.mybatis.dialect.Dialect;
import ac.zlf.springmybatis.core.mybatis.domain.PageRequest;
import ac.zlf.springmybatis.core.mybatis.domain.Sort;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.Environment;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.session.Configuration;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class SQLHelper {
    private static Logger logger = LoggerFactory.getLogger(SQLHelper.class);

    public static String getCountString(String querySelect) {
        querySelect = getLineSql(querySelect);
        int orderIndex = getLastOrderInsertPoint(querySelect);
        int formIndex = getAfterFormInsertPoint(querySelect);
        String select = querySelect.substring(0, formIndex);
        if ((select.toLowerCase().indexOf("select distinct") != -1) || (querySelect.toLowerCase().indexOf("group by") != -1)) {
            return  "select count(1) count from (" + querySelect.substring(0, orderIndex) + " ) t";
        }
        return "select count(1) count " + querySelect.substring(formIndex, orderIndex);
    }

    public static String getLineSql(String sql) {
        return sql.replaceAll("[\r\n]", " ").replaceAll("\\s{2,}", " ");
    }

    public static int getLastOrderInsertPoint(String querySelect) {
        int orderIndex = querySelect.toLowerCase().lastIndexOf("order by");
        if ((orderIndex == -1) || (!isBracketCanPartnership(querySelect.substring(orderIndex, querySelect.length())))) {
            throw new RuntimeException("My SQL 分页必须要有Order by 语句!");
        }
        return orderIndex;
    }

    private static int getAfterFormInsertPoint(String querySelect) {
        String regex = "\\s+FROM\\s+";
        Pattern pattern = Pattern.compile(regex, 2);
        Matcher matcher = pattern.matcher(querySelect);
        while (matcher.find()) {
            int fromStartIndex = matcher.start(0);
            String text = querySelect.substring(0, fromStartIndex);
            if (isBracketCanPartnership(text)) {
                return fromStartIndex;
            }
        }
        return 0;
    }

    private static boolean isBracketCanPartnership(String text) {
        if ((text == null) || (getIndexOfCount(text, '(') != getIndexOfCount(text, ')'))) {
            return false;
        }
        return true;
    }

    private static int getIndexOfCount(String text, char ch) {
        int count = 0;
        for (int i = 0; i < text.length(); i++) {
            count = text.charAt(i) == ch ? count + 1 : count;
        }
        return count;
    }

    public static PageRequest rebuildPageRequest(int total, PageRequest pageRequest) {
        int maxPage = (int) Math.ceil(total / pageRequest.getPageSize());
        if (pageRequest.getPageNumber() > maxPage - 1) {
            pageRequest = new PageRequest(maxPage, pageRequest.getPageSize());
        }
        return pageRequest;
    }

    public static int getCount(MappedStatement mappedStatement, Object parameterObject, BoundSql boundSql, Dialect dialect)
            throws SQLException {
        String count_sql = dialect.getCountSQL();
        logger.debug("Total count SQL [{}] ", count_sql);
        logger.debug("Total count Parameters: {} ", parameterObject);

        Connection connection = null;
        PreparedStatement countStmt = null;
        ResultSet rs = null;
        try {
            connection = mappedStatement.getConfiguration().getEnvironment().getDataSource().getConnection();
            countStmt = connection.prepareStatement(count_sql);
            DefaultParameterHandler handler = new DefaultParameterHandler(mappedStatement, parameterObject, boundSql);
            handler.setParameters(countStmt);

            rs = countStmt.executeQuery();
            int count = 0;
            if (rs.next()) {
                count = rs.getInt(1);
            }
            logger.debug("Total count: {}", Integer.valueOf(count));
            return count;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } finally {
                try {
                    if (countStmt != null) {
                        countStmt.close();
                    }
                } finally {
                    if ((connection != null) && (!connection.isClosed())) {
                        connection.close();
                    }
                }
            }
        }
    }

    public static String getLimitString(String querySelect, int offset, int limit, Sort sort) {
        querySelect = getLineSql(querySelect);
        if (sort != null) {
            int orderIndex = getLastOrderInsertPoint(querySelect);
            StringBuffer sb = new StringBuffer(querySelect.substring(0, orderIndex) + " order by ");
            Iterator<Sort.Order> orderIt = sort.iterator();
            while (orderIt.hasNext()) {
                Sort.Order order = (Sort.Order) orderIt.next();
                sb.append(String.format("%s %s,", new Object[]{order.getProperty(), order.getDirection()}));
            }
            querySelect = sb.toString().substring(0, sb.length() - 1);
        }
        String sql = querySelect + " limit " + offset + " ," + limit;
        return sql;
    }

    public static void main(String[] args) {
        String xx = "select \n \n distinct     \n    COP_ID, COP_CAT_ID, COP_NAME, COP_START_DT, COP_END_DT,\n    COP_VALID_IND, COP_SHOP_NAME, COP_SHOP_PHONE, COP_SHOP_ADDR, COP_AVERAGE_SPEND, UPDATE_USER, \n    CREATE_USER, UPDATE_DT, CREATE_DT\n   \n    from coupon\n     WHERE  COP_CAT_ID = ? \n     order by COP_ID ";


        System.out.println(getCountString(xx));

        getLimitString(xx, 2, 10, new Sort(new Sort.Order[]{new Sort.Order(Sort.Direction.ASC, "id"), new Sort.Order(Sort.Direction.DESC, "fullName")}));
    }
}